if exists (select 1 from sysobjects where name = 'vw_personferien' and type = 'V')
begin
   drop view vw_personferien
   print 'View: vw_personferien deleted ...'
end
go
create view vw_personferien
as
   select 
      prsid  = p.PrsId,
   nachname  = p.Nachname,
    vorname  = p.Vorname, 
    strasse  = p.Strasse + ' ' + p.Strassennr,
   postfach  = p.Postfach,
        PLZ  = p.PLZ,
        ort  = p.Ort,
     telnrp  = p.TelnrP,
     telnrn  = p.TelnrN,
      email  = p.Email,
   ferienid = f.FerienID,
  ferienvon = f.Von,
  ferienbis = f.Bis
     from Person p
     left outer join Ferien f
       on f.PrsId = p.PrsId
where p.PersonTyp = 1

go
print 'View: vw_personferien done ...'
go

grant select on vw_personferien to prsadmins with grant option
go
grant select on vw_personferien to prsusers
go
